﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using QuanLyHocPhi.Entity;

/// <summary>
/// Summary description for ObjectDAO
/// </summary>
namespace QuanLyHocPhi.DAL
{
    public class DOI_TUONG_DAO
    {
        private const string SQL_SELECT_ALL = "select distinct MA_DOI_TUONG,TEN_DOI_TUONG, LOP,GHI_CHU from DOI_TUONG";
        private const string SQL_SELECT_OBJECT = "select MA_DOI_TUONG,TEN_DOI_TUONG, LOP,GHI_CHU from DOI_TUONG where DOI_TUONG.MA_DOI_TUONG = @MA_DOI_TUONG";
        private const string SQL_SELECT_OBJECTS_BY_LOP = "select MA_DOI_TUONG,TEN_DOI_TUONG, [LOP],GHI_CHU from DOI_TUONG where [LOP] = @LOP";
        private const string SQL_INSERT = "insert into DOI_TUONG(TEN_DOI_TUONG, LOP, GHI_CHU) values(@TEN_DOI_TUONG, @LOP, @GHI_CHU)";
        private const string SQL_UPDATE = "update DOI_TUONG set TEN_DOI_TUONG = @TEN_DOI_TUONG, LOP = @LOP,GHI_CHU = @GHI_CHU where MA_DOI_TUONG = @MA_DOI_TUONG";
        private const string SQL_DELETE = "delete from [DOI_TUONG] where MA_DOI_TUONG = @MA_DOI_TUONG";
        private const string SQL_SELECT_OBJLOP = "select distinct [LOP] from [DOI_TUONG]";

        // Parameters
        private const string PARM_OBJECT_ID = "@MA_DOI_TUONG";
        private const string PARM_OBJECT_LOP = "@LOP";
        private const string PARM_OBJECT_NAME = "@TEN_DOI_TUONG";
        private const string PARM_OBJECT_GHI_CHU = "@GHI_CHU";

        #region Public Methods
        public IList<QuanLyHocPhi.Entity.DOI_TUONG> GetAll()
        {
            IList<QuanLyHocPhi.Entity.DOI_TUONG> objects = new List<QuanLyHocPhi.Entity.DOI_TUONG>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_ALL, null))
            {
                while (reader.Read())
                {
                    QuanLyHocPhi.Entity.DOI_TUONG obj = new QuanLyHocPhi.Entity.DOI_TUONG();
                    obj.MA_DOI_TUONG = reader.GetInt32(reader.GetOrdinal("MA_DOI_TUONG"));
                    obj.TEN_DOI_TUONG = reader.GetString(reader.GetOrdinal("TEN_DOI_TUONG"));
                    obj.LOP = reader.GetString(reader.GetOrdinal("LOP"));
                    obj.GHI_CHU = reader.GetString(reader.GetOrdinal("GHI_CHU"));
                    objects.Add(obj);
                }
            }
            return objects;
        }

        public IList<QuanLyHocPhi.Entity.DOI_TUONG> GetObjectsByLOP(string LOPOfObject)
        {
            IList<QuanLyHocPhi.Entity.DOI_TUONG> objects = new List<QuanLyHocPhi.Entity.DOI_TUONG>();
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_OBJECT_LOP, SqlDbType.NVarChar)};
            parms[0].Value = LOPOfObject;

            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_OBJECTS_BY_LOP, parms))
            {
                while (reader.Read())
                {
                    QuanLyHocPhi.Entity.DOI_TUONG obj = new QuanLyHocPhi.Entity.DOI_TUONG();
                    obj.MA_DOI_TUONG = reader.GetInt32(reader.GetOrdinal("MA_DOI_TUONG"));
                    obj.TEN_DOI_TUONG = reader.GetString(reader.GetOrdinal("TEN_DOI_TUONG"));
                    obj.LOP = reader.GetString(reader.GetOrdinal("LOP"));
                   if(!reader.IsDBNull(3)) obj.GHI_CHU = reader.GetString(reader.GetOrdinal("GHI_CHU"));
                    objects.Add(obj);
                }
            }
            return objects;
        }

        public QuanLyHocPhi.Entity.DOI_TUONG GetObject(int id)
        {
            QuanLyHocPhi.Entity.DOI_TUONG obj = new QuanLyHocPhi.Entity.DOI_TUONG();
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_OBJECT_LOP, SqlDbType.Int)};
            parms[0].Value = id;

            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_OBJECT, parms))
            {
                while (reader.Read())
                {
                    obj.MA_DOI_TUONG = reader.GetInt32(reader.GetOrdinal("MA_DOI_TUONG"));
                    obj.TEN_DOI_TUONG = reader.GetString(reader.GetOrdinal("TEN_DOI_TUONG"));
                    obj.LOP = reader.GetString(reader.GetOrdinal("LOP"));
                    obj.GHI_CHU = reader.GetString(reader.GetOrdinal("GHI_CHU"));
                }
            }
            return obj;
        }

        public IList<string> GetAllObjectLOP()
        {
            IList<string> list = new List<string>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_OBJLOP, null))
            {
                while (reader.Read())
                {
                    string st = reader.GetString(reader.GetOrdinal("LOP"));
                    list.Add(st);
                }
            }
            return list;
        }

        public Boolean Insert(QuanLyHocPhi.Entity.DOI_TUONG obj)
        {
            {
                SqlParameter[] parms;
                parms = new SqlParameter[] {
											 new SqlParameter(PARM_OBJECT_NAME, SqlDbType.NVarChar),
											 new SqlParameter(PARM_OBJECT_LOP, SqlDbType.NVarChar),
                                             new SqlParameter(PARM_OBJECT_GHI_CHU, SqlDbType.NVarChar)};
                parms[0].Value = obj.TEN_DOI_TUONG;
                parms[1].Value = obj.LOP;
                parms[2].Value = obj.GHI_CHU;

                try
                {
                    int result = 0;
                    SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
                    result = SQLDBHelper.ExecuteNonQuery(conn,
                        CommandType.Text, SQL_INSERT, parms);
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                    return (result > 0 ? true : false);
                }
                catch (Exception e)
                {
                    throw e;
                }
                //return false;
            }
        }

        public bool Update(QuanLyHocPhi.Entity.DOI_TUONG obj)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_OBJECT_NAME, SqlDbType.NVarChar),
											 new SqlParameter(PARM_OBJECT_LOP, SqlDbType.NVarChar),
                                             new SqlParameter(PARM_OBJECT_GHI_CHU, SqlDbType.NVarChar),
                                             new SqlParameter(PARM_OBJECT_ID, SqlDbType.Int)};
            parms[0].Value = obj.TEN_DOI_TUONG;
            parms[1].Value = obj.LOP;
            parms[2].Value = obj.GHI_CHU;
            parms[3].Value = obj.MA_DOI_TUONG;

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                    CommandType.Text, SQL_UPDATE, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            //return false;
        }

        public bool Delete(int id)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARM_OBJECT_ID, SqlDbType.Int) };
            parms[0].Value = id;

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                    CommandType.Text, SQL_DELETE, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            //return false;
        }
        #endregion
    }
}